Hi,

Index PK_common_Dimuser  for Primary key .

UK_Dimuser_PUID is creating part of the create script.  How the UI_common_Dimuser will be created.  UK_Dimuser_PUID & UI_common_Dimuser  created against same column. How do we eliminate such a issues?

Thanks in Advance. Wish you a happy new year 2014.

Thanks

Thiru

Quick Google search returns this page

http://www.sswug.org/articlesection/default.aspx?TargetID=64974

Try googling on 'Duplicate index check SQL Server'

Itzik Ben-Gan wrote the below script

The first query finds exact matches. The indexes must have 
the same key columns in the same order, and the same included columns but in any order. 
These indexes are sure targets for elimination. The only caution would be to check for index hints. 

 
-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

 
The second variation of this query finds partial, or duplicate, indexes 
that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) 
would be considered duplicate indexes. This query only examines key columns and does not consider included columns. 
These types of indexes are probable dead indexes walking. 

-- Overlapping indxes
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + '%' 
or c2.cols like c1.cols + '%') ;

Be careful when dropping a partial duplicate index if the two indexes differ greatly in width. 
For example, if Ix1 is a very wide index with 12 columns, and Ix2 is a narrow two-column index 
that shares the first two columns, you may want to leave Ix2 as a faster, tighter, narrower index.

Another script available at http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

Use tempdb
go
Create table Common_DimUser
(
PUser varchar(10) not NULL,
PFName varchar(10),
PLName varchar(10)
)
GO
ALTER TABLE dbo.Common_DimUser ADD CONSTRAINT PK_Common_DimUser PRIMARY KEY CLUSTERED (PUser) 
go
CREATE  NONCLUSTERED INDEX [UI_Dimuser_PUID] ON [dbo].[Common_DimUser]([PUser] ASC)
GO
ALTER TABLE [Common_DimUser] ADD CONSTRAINT UK_Dimuser_PUID UNIQUE(PUser)
GO

Hi Balmukund,

why we need to  create  non-clustered twice against PUser column?

Thanks

Thiru

Hi Balmukund,

why we need to  create  non-clustered twice against PUser column?

Thanks

Thiru

Hi

No ,you do not have otherwise you have another column or columns included in the index